package com.demo.connectdbtest.export;

import com.demo.connectdbtest.export.vo.Promotor;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.util.Date;

@Slf4j
public class ExportTest {
    public static void main(String[] args) throws Exception {
        long t1 = new Date().getTime();
        excel();
        long t2 = new Date().getTime();

        System.out.println((t2-t1)/1000 + "秒");
    }

    public static void excel() throws Exception {
        File file = new File("C:\\推广进入语音房数据.xlsx");
        if (!file.exists()){
            throw new Exception("文件不存在!");
        }
        InputStream in = new FileInputStream(file);

        // 读取整个Excel
        XSSFWorkbook sheets = new XSSFWorkbook(in);
        // 获取第一个表单Sheet
        XSSFSheet sheetAt = sheets.getSheetAt(0);

        //默认第一行为标题行，i = 0
        XSSFRow titleRow = sheetAt.getRow(0);
        // 循环获取每一行数据
        for (int i = 1; i < sheetAt.getPhysicalNumberOfRows(); i++) {
            XSSFRow row = sheetAt.getRow(i);
            // 读取每一格内容
            StringBuilder sb = new StringBuilder();
            for (int index = 0; index < row.getPhysicalNumberOfCells(); index++) {
                XSSFCell titleCell = titleRow.getCell(index);
                XSSFCell cell = row.getCell(index);
//                 cell.setCellType(CellType.STRING);
                System.out.println(cell+"---------------");
                DataFormatter formatter = new DataFormatter();
//               ;//直接获取到单元格的值
               // System.out.println( formatter.formatCellValue(cell));
                System.out.println( cell.getCellFormula());
//                log.debug("行==index{}，content={}",index, JSONObject.toJSONString(cell));
//                cell.setCellType(CellType.STRING);
                CellType cellType = cell.getCellType();
//                int cellType = cell.getCellType();
                System.out.println(cellType+"------");
                if (cell.getStringCellValue().equals("") || StringUtils.isEmpty(cell.getStringCellValue())) {
                    continue;
                }
//                log.debug("========={}",JSONObject.toJSONString(cell.getStringCellValue()));
                Promotor promotor = new Promotor();
                promotor.setUid(0L);
                promotor.setNickname("");
                promotor.setPromoteGuild("");



                sb.append(cell);
            }
            System.out.println(i + "\t" + sb);
        }
    }

//    原文链接：https://blog.csdn.net/qq_29384639/article/details/121749397
//
//    public static String getCellValue(Cell cell, Workbook workbook){
//        if(cell==null){
//            return "";
//        }
//
//        if(cell.equals(CellType.STRING)){//字符串
//            return cell.getRichStringCellValue().getString();
//        }else if(cell.equals(CellType.NUMERIC)){//数值
//            if (DateUtil.isCellDateFormatted(cell)) {//日期类型
//                return cell.getDateCellValue();
//            } else {//数值类型
//                return cell.getNumericCellValue();
//            }
//        }else if(cell.equals(CellType.FORMULA)){//公式类型
//            FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
//            return evaluator.evaluate(cell).getNumberValue()+"";
//        }else if(cell.equals(CellType.BOOLEAN)){//布尔类型
//            return cell.getBooleanCellValue()? "TRUE" : "FALSE";
//        }else{
//            return "";
//        }
//
//    }






}
